package com.feib.soeasy.service.impl;


import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.feib.soeasy.dao.CommissionCodeTableDao;
import com.feib.soeasy.dao.Soez6001Dao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.pagination.PaginatedList;
import com.feib.soeasy.service.Soez6001Service;
import com.feib.soeasy.util.CodeTableUtil;
import com.feib.soeasy.util.UserUtil;



@Service("soez6001Service")
public class Soez6001ServiceImpl extends BaseServiceImpl implements Soez6001Service {


	protected Logger logger = LoggerFactory.getLogger(this.getClass());


	@Resource(name="soez6001Dao")
	private Soez6001Dao soez6001Dao;


	@Resource(name="commissionCodeTableDao")
	public CommissionCodeTableDao commissionCodeTableDao;


	/**
	 * 暫存目錄
	 */
	@Resource(name = "tempDirectory")
	private String tempDirectory;

	/**  取得各通路撥款總計 */
	public PaginatedList<Map<String, Object>> queryTransferLogByGroup(Group group, String channels[],
			Date[] dateFrom, Date[] dateTo,PaginatedList<Map<String, Object>> paginationConfig) {

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		PaginatedList<Map<String, Object>> fbLogs = soez6001Dao.queryTransferLog(group, channels, dateFrom, dateTo, paginationConfig);

		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Map<String, Object>> results = new PaginatedList<Map<String, Object>>();				
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);


		List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	

			//個別Code統計值
			Long codeCount = new Long(0);
			Long codeAmount = new Long(0);


			//計算手續費
			codeAmount = ((BigDecimal)fblog.get("realAmount")).longValue();// REAL_AMOUNT
			codeCount = ((BigDecimal)fblog.get("totalCount")).longValue();//TOTAL_COUNT

			//加統計值
			totalCount += codeCount;
			totalAmount += codeAmount;

			fblog.put("allCount", totalCount);
			fblog.put("allAmount", totalAmount);

			/*計算總計*/
			temp.add(fblog);
		}


		results.setPaginatedList(temp);
		/* 清除資源*/
		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}

	/**
	 *  取得放大鏡資料 FeedbackLog (總計明細) */
	public PaginatedList<Map<String, Object>> queryGroupFeedbackLogByChannel(Group group, String channels[],
			Date dateFroms[], Date dateTos[],PaginatedList<Map<String, Object>> paginationConfig) {
		/*
		 *	畫面上的總計放大鏡
		 *	應該要傳所查詢的通路跟各通路的時間
		 */
		Map<String, String> channelCode=new HashMap<String, String>();

		channelCode.put( "7111111","D");
		channelCode.put("TFM","E" );
		channelCode.put( "HILIFE","F");
		channelCode.put( "OKCVS","G");
		channelCode.put("POST","H");



		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		PaginatedList<Map<String, Object>> fbLogs = soez6001Dao.queryTotalGroupFeedbackLog(group, channels, dateFroms, dateTos, paginationConfig);

		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Map<String, Object>> results = new PaginatedList<Map<String, Object>>();				
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());

		List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	
			/* 總計明細用的總計 顯示在最下方,最後一頁再顯示 */
			if(fbLogs.isLastPage()){	
				@SuppressWarnings("unchecked")
				Map<String, Object> total=(Map<String, Object>) fbLogs.getTotalObject();
				fblog.put("totalCount", total.get("totalCount"));
				fblog.put("totalAmount", ((BigDecimal)total.get("totalAmount")).longValue());
				fblog.put("totalFee", ((BigDecimal)total.get("totalFee")).longValue());
			}
			/*計算總計*/
			temp.add(fblog);

		}

		results.setPaginatedList(temp);
		/* 清除資源*/
		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}


	/**
	 *查詢主畫面excel 
	 */
	@Override
	public String doExportGroupStatisticsXls(Group group, String[] channels,
			Date[] dateFroms, Date[] dateTos) {

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商撥款統計表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*25);
		sheet.setColumnWidth(1, 256*10);
		sheet.setColumnWidth(3, 256*20);
		sheet.setColumnWidth(4, 256*20);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局撥款統計表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(channel != null && !"".equals(channel))
			{
				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel)  + "：" + dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("title2"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$H$" + rownum));
			}

		}


		//串欄位表頭
		String[] titles = {"NO",
				"撥款日期","業者代號","實體帳號","業者名稱","通路" ,"代收筆數", "入帳金額"};

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;



		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		//總計數據
		Long totalCount = new Long(0);
		Long totalRealAmount = new Long(0);


		PaginatedList<Map<String, Object>>queryResult = new PaginatedList<Map<String,Object>>();
		/* 保持顯示全部不分頁*/
		queryResult.setPageSize(-1);

		PaginatedList<Map<String, Object>> result = soez6001Dao.queryTransferLog(group, channels, dateFroms, dateTos, queryResult);


		//TODO 動態回圈
		List<Map<String, Object>> fbLogs = result.getPaginatedList();

		
		int no=0;//序號
		
		if (null != fbLogs && 0 != fbLogs.size())
		{	int cellRangeAddressStart=0;
			for(Map<String, Object> channelData : fbLogs)
			{	
				  
				no++;
				
				int cellRangeAddressEnd = 0;
				for(int i=0;i<channels.length;i++)
				{
					String channel = channels[i];
					if(channel != null && !"".equals(channel))
					{

						Row dataRow = null; 
						Cell cell = null;
						//int mergeRownum = sheet.getLastRowNum() + 1;
						/*計算合併欄位的位置*/
						
						cellRangeAddressStart=sheet.getLastRowNum()-2 ;					
						
						  cellRangeAddressEnd = 0;

						/* 一圈就是一個日期區間的資料 */

						
						Date transferTime = (Date)channelData.get("transferTime");//撥款日期
						String groupNo = (String)channelData.get("groupNo");//業者代號
						String  accountNo= (String)channelData.get("accountNo");//實體帳號
						String  groupName= (String)channelData.get("groupName");//業者名稱
						//String channel = (String)channelData.get("CHANNEL");        	
						//String channelName = (String)channelData.get("CHANNEL_NAME");//通路名稱



						dataRow = sheet.createRow(rownum++);
						if (0>= cellRangeAddressEnd)
							cellRangeAddressEnd = rownum;
						dataRow.setHeightInPoints((float) 25);


						cell = dataRow.createCell(0);//序號
						cell.setCellValue(no);
						cell.setCellStyle(styles.get("cell"));


						cell = dataRow.createCell(1);//撥款日期
						if (transferTime==null)
							cell.setCellValue("N/A");
						else
							cell.setCellValue(dateFormat.format(transferTime));
						cell.setCellStyle(styles.get("cell_date"));

						cell = dataRow.createCell(2);//業者代號
						cell.setCellValue(groupNo);
						cell.setCellStyle(styles.get("cell"));

						cell = dataRow.createCell(3);//實體帳號
						cell.setCellValue(accountNo);
						cell.setCellStyle(styles.get("cell"));

						cell = dataRow.createCell(4);//業者名稱
						cell.setCellValue(groupName);
						cell.setCellStyle(styles.get("cell"));





						/*超商欄位*/
						String  codeName = CodeTableUtil.getInstance().getCodeName("CHANEL", channel) ;//通路名稱
						//logger.debug("codeName {}",codeName);
						//logger.debug("channel {}",channel);
						//TODO 沒有的不顯示
						int channelCount = (int)((BigDecimal) channelData.get(channel+"Count")).longValue();
						Long channelAmount = ((BigDecimal)channelData.get(channel+"Amount")).longValue();
						//logger.debug("channelCount {}",channelCount);

						cell = dataRow.createCell(5);
						cell.setCellValue(codeName);
						cell.setCellStyle(styles.get("cell"));

						if(channelCount>0){
							cell = dataRow.createCell(6);
							cell.setCellValue(channelCount);
							cell.setCellStyle(styles.get("cell"));

							cell = dataRow.createCell(7);
							cell.setCellValue(channelAmount);
							cell.setCellStyle(styles.get("cell_number"));
						}else{
							cell = dataRow.createCell(6);

							cell.setCellValue("查無資料 ");
							cell.setCellStyle(styles.get("cell"));
							cell = dataRow.createCell(7);
							cell.setCellStyle(styles.get("cell"));
							sheet.addMergedRegion(CellRangeAddress.valueOf("$G$"+rownum+":$H$" + rownum));

						}
					

					}

				
					
				}
				
				/*TODO  合併前五格*/
				String[] mergeRow={"A","B","C","D","E"};	
					
				for(int i=0;i<=4;i++){
					//ex:"$A$"+rownum+":$I$" + rownum  "A$9"
					//sheet.addMergedRegion(CellRangeAddress.valueOf("$"+mergeRow[0]+"$"+cellRangeAddress+":$"+mergeRow[0]+"$"+(cellRangeAddress+lastIndex)));            
					sheet.addMergedRegion(CellRangeAddress.valueOf("$"+mergeRow[i]+"$"+(cellRangeAddressStart)+":$"+mergeRow[i]+"$"+(cellRangeAddressEnd)));            
					logger.debug("cellRangeAddressStart {}",cellRangeAddressStart);	
					logger.debug("cellRangeAddressEnd {}",cellRangeAddressEnd);
					}
				
				
				
				/*TODO 統計*/
				Row dataCountRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataCountRow.createCell(0);
				dataCell.setCellValue("統計");
				dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
				dataCell = dataCountRow.createCell(1);
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataCountRow.createCell(2);
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataCountRow.createCell(3);	
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataCountRow.createCell(4);	
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataCountRow.createCell(5);				
				dataCell.setCellStyle(styles.get("cell"));

				int groupTotalCount =(int)((BigDecimal) channelData.get("totalCount")).longValue();
				Long groupRealAmount = ((BigDecimal)channelData.get("realAmount")).longValue();

				dataCell = dataCountRow.createCell(6);
				dataCell.setCellValue(groupTotalCount);
				dataCell.setCellStyle(styles.get("cell"));

				dataCell = dataCountRow.createCell(7);
				dataCell.setCellValue(groupRealAmount);
				dataCell.setCellStyle(styles.get("cell_number"));
				
				 /* 統計所有數據 */
				 totalCount += groupTotalCount;
				 totalRealAmount += groupRealAmount;

				

				//合併
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$F$" + rownum));

			}

		}

		/*TODO 總計*/
		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));
		dataCell = dataRow.createCell(0);
		dataCell.setCellValue("統計");
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(1);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(2);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);	
		dataCell.setCellStyle(styles.get("cell"));
	
		dataCell = dataRow.createCell(4);				
		dataCell.setCellValue("超商／郵局合計");
		dataCell.setCellStyle(styles.get("cell"));
	
		dataCell = dataRow.createCell(5);				
		dataCell.setCellStyle(styles.get("cell"));
		/*  總計記數 */		
		dataCell = dataRow.createCell(6);
		dataCell.setCellValue(totalCount);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(7);
		dataCell.setCellValue(totalRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-超商傳輸統計表.xls"; //

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}



	@Override
	public String doExportGroupDetailStatisticsXls(Group group,
			String[] channels, Date[] dateFroms, Date[] dateTos) {
		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商郵局撥款統計明細表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*25);
		sheet.setColumnWidth(1, 256*10);
		sheet.setColumnWidth(3, 256*20);
		sheet.setColumnWidth(4, 256*20);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商/郵局撥款統計明細表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		/* 顯示通路跟區間*/
		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(channel != null && !"".equals(channel))
			{
				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel)  + "：" + dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("title2"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$H$" + rownum));
			}

		}

		//串欄位表頭
		String[] titles = {
				"撥款日期","繳費期限","繳費日期" ,"客戶名稱", "繳費帳號", "繳費金額" , "代收手續費","實撥金額"};

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;

		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		//總計數據
		int totalCount = 0;
		Long totalAmount = new Long(0);
		Long totalFee = new Long(0);
		Long totalRealAmount = new Long(0);




		PaginatedList<Map<String, Object>>queryResult = new PaginatedList<Map<String,Object>>();
		/* 單通路單代碼明細資料保持顯示全部不分頁*/
		queryResult.setPageSize(-1);
		PaginatedList<Map<String, Object>> result = soez6001Dao.queryTotalGroupFeedbackLog(group, channels, dateFroms, dateTos, queryResult);

		List<Map<String, Object>> fbLogs = result.getPaginatedList();


		for(Map<String, Object>fblog:fbLogs)
		{

			long txamt = ((BigDecimal)fblog.get("txamt")).longValue();  

			long fee =((BigDecimal)fblog.get("fee")).longValue();					

			Row dataRow = sheet.createRow(rownum++);
			Cell dataCell;
			dataCell = dataRow.createCell(0);
			Date transferTime = (Date)fblog.get("transferTime");
			if (transferTime==null)
				dataCell.setCellValue("N/A");
			else
				dataCell.setCellValue(dateFormat.format(transferTime));
			dataCell.setCellStyle(styles.get("cell_date"));
			dataCell = dataRow.createCell(1);

			Date dueday = (Date)fblog.get("dueday");
			if (dueday==null)
				dataCell.setCellValue("N/A");
			else
				dataCell.setCellValue(dateFormat.format(dueday));

			dataCell.setCellStyle(styles.get("cell_date"));
			dataCell = dataRow.createCell(2);
			String aday = (String)fblog.get("aday");
			dataCell.setCellValue(aday.substring(0,4)+"/"+aday.substring(4,6)+"/"+aday.substring(6,8));
			dataCell.setCellStyle(styles.get("cell_date"));
			dataCell = dataRow.createCell(3);
			dataCell.setCellValue((String)fblog.get("payerName"));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(4);
			dataCell.setCellValue((String)fblog.get("userno"));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(5);
			dataCell.setCellValue(txamt);
			dataCell.setCellStyle(styles.get("cell_number"));
			dataCell = dataRow.createCell(6);
			dataCell.setCellValue(fee);
			dataCell.setCellStyle(styles.get("cell_number"));
			dataCell = dataRow.createCell(7);
			dataCell.setCellValue(txamt-fee);
			dataCell.setCellStyle(styles.get("cell_number"));


		}

		@SuppressWarnings("unchecked")
		Map<String, Object> total=(Map<String, Object>) result.getTotalObject();

		totalCount = (Integer) total.get("totalCount");
		totalAmount = ((BigDecimal)total.get("totalAmount")).longValue();
		totalFee = ((BigDecimal)total.get("totalFee")).longValue();
		totalRealAmount =totalAmount-totalFee;



		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;
		dataCell = dataRow.createCell(0);
		dataCell.setCellValue("總計");
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(1);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(2);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);
		dataCell.setCellStyle(styles.get("cell"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));

		dataCell = dataRow.createCell(4);
		dataCell.setCellValue(totalCount+"筆");
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(5);
		dataCell.setCellValue(totalAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(6);
		dataCell.setCellValue(totalFee);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(7);
		dataCell.setCellValue(totalRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));



		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-超商傳輸統計表.xls"; //

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}

	/**
	 * Create a library of cell styles
	 */
	private static Map<String, CellStyle> createStyles(Workbook wb){
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		CellStyle style;
		Font titleFont = wb.createFont();
		titleFont.setFontName("標楷體");
		titleFont.setFontHeightInPoints((short)24);
		style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFont(titleFont);
		styles.put("title", style);

		Font title2Font = wb.createFont();
		title2Font.setFontName("標楷體");
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題
		title2Font.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(title2Font);        
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styles.put("title2", style);

		Font headerFont = wb.createFont();
		headerFont.setFontName("標楷體");
		headerFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(headerFont);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("header", style);

		Font cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell", style);

		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setWrapText(false);//設定文字是否換行
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_ALIGN_RIGHT", style);

		style = wb.createCellStyle();
		Font cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number", style);

		style = wb.createCellStyle();
		Font cellDateFont = wb.createFont();
		cellDateFont.setFontName("Arial");
		cellDateFont.setFontHeightInPoints((short)12);
		style.setFont(cellDateFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
		styles.put("cell_date", style);


		cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)18);
		cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_sub_total", style);

		style = wb.createCellStyle();
		cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number_sub_total", style);




		cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)14);
		cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_total", style);

		style = wb.createCellStyle();
		cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number_total", style);

		return styles;
	}

}
